import pandas as pd
from common_import import *


def 处理利润():
    # 读取 CSV 文件
    df = pd.read_csv("data/2_2023年统计的相关数据.csv")

    # 修改列名为英文
    df.columns = [
        "Index",
        "CropID",
        "CropName",
        "FieldType",
        "PlantingSeason",
        "Yield_per_Mu",
        "Cost_per_Mu",
        "Price_per_Jin",
    ]

    # 分隔销售单价为最低和最高单价，并计算平均销售单价
    df[["MinPrice", "MaxPrice"]] = df["Price_per_Jin"].str.split("-", expand=True)
    df["MinPrice"] = df["MinPrice"].astype(float)
    df["MaxPrice"] = df["MaxPrice"].astype(float)
    df["AvgPrice"] = (df["MinPrice"] + df["MaxPrice"]) / 2

    # 计算每亩利润
    df["Profit_per_Mu"] = df["Yield_per_Mu"] * df["AvgPrice"] - df["Cost_per_Mu"]

    # 计算每斤利润
    df["Profit_per_Jin"] = df["AvgPrice"] - (df["Cost_per_Mu"] / df["Yield_per_Mu"])
    # 保留两位小数
    df["AvgPrice"] = df["AvgPrice"].round(4)
    df["Profit_per_Mu"] = df["Profit_per_Mu"].round(4)
    df["Profit_per_Jin"] = df["Profit_per_Jin"].round(4)

    # 选择需要的列
    df_result = df[
        [
            "Index",
            "CropID",
            "CropName",
            "FieldType",
            "PlantingSeason",
            "Yield_per_Mu",
            "Cost_per_Mu",
            "MinPrice",
            "MaxPrice",
            "AvgPrice",
            "Profit_per_Mu",
            "Profit_per_Jin",
        ]
    ]

    # 保存处理后的数据到新的 CSV 文件
    df_result.to_csv("data/利润.csv", index=False)


def 补全种植地缺失():
    df = pd.read_csv("data/2_2023年的农作物种植情况.csv")

    # 使用 fillna 方法向前填充缺失值
    df["种植地块"] = df["种植地块"].fillna(method="ffill")

    # 将补全后的数据输出到新的 CSV 文件中
    df.to_csv("data/2_每个地种什么.csv", index=False)


def 添加销量列():
    df_land = pd.read_csv("data/2_每个地种什么.csv")
    # 为每个种植地块计算总产量
    df_land["总产量/斤"] = df_land.apply(
        lambda row: row["种植面积/亩"]
        * mapping.get_yield_per_acre(
            row["作物编号"], mapping.get_land_type(row["种植地块"]), row["种植季次"]
        ),
        axis=1,
    )

    # 保存结果到新的CSV文件
    df_land.to_csv("second_file_with_total_yield.csv", index=False)


def 处理总产量1():
    # 读取数据
    df = pd.read_csv("second_file_with_total_yield.csv")

    # 过滤出种植地块以 A, B, C 开头的行
    df_filtered = df[df["种植地块"].str.startswith(("A", "B", "C"))].copy()

    # 将种植地块名称改为 "前三类"
    df_filtered["种植地块"] = "前三类"

    # 按照作物编号和其他列合并数据并计算总产量
    df_grouped = (
        df_filtered.groupby(
            ["种植地块", "作物编号", "作物名称", "作物类型", "种植季次"]
        )
        .agg({"总产量/斤": "sum"})
        .reset_index()
    )

    # 处理以 D 开头的行，改为 "水浇地"
    df_d = df[df["种植地块"].str.startswith("D")].copy()
    df_d["种植地块"] = "水浇地"
    df_d_grouped = (
        df_d.groupby(["种植地块", "作物编号", "作物名称", "作物类型", "种植季次"])
        .agg({"总产量/斤": "sum"})
        .reset_index()
    )

    # 处理以 E 开头的行，改为 "普通大棚"
    df_e = df[df["种植地块"].str.startswith("E")].copy()
    df_e["种植地块"] = "普通大棚"
    df_e_grouped = (
        df_e.groupby(["种植地块", "作物编号", "作物名称", "作物类型", "种植季次"])
        .agg({"总产量/斤": "sum"})
        .reset_index()
    )

    # 处理以 F 开头的行，改为 "智慧大棚"
    df_f = df[df["种植地块"].str.startswith("F")].copy()
    df_f["种植地块"] = "智慧大棚"
    df_f_grouped = (
        df_f.groupby(["种植地块", "作物编号", "作物名称", "作物类型", "种植季次"])
        .agg({"总产量/斤": "sum"})
        .reset_index()
    )

    # 过滤出不是以 A, B, C, D, E, F 开头的行
    df_not_abc_def = df[
        ~df["种植地块"].str.startswith(("A", "B", "C", "D", "E", "F"))
    ].copy()

    # 合并所有部分的数据
    df_result = pd.concat(
        [df_grouped, df_d_grouped, df_e_grouped, df_f_grouped, df_not_abc_def],
        ignore_index=True,
    )
    category_order = ["前三类", "水浇地", "普通大棚", "智慧大棚"]

    # 将地块类型列设置为有序分类数据类型
    df_result["种植地块"] = pd.Categorical(
        df_result["种植地块"], categories=category_order, ordered=True
    )
    df_sorted = df_result.sort_values(
        by=["种植地块", "种植季次", "作物编号"], ascending=[True, True, True]
    )
    # 保存结果到 CSV 文件
    df_sorted.to_csv("combined_file.csv", index=False)


def 计算降价后利润():
    df = pd.read_csv("data/利润.csv")  # 请将 'your_file.csv' 替换为你的文件路径

    # 计算每斤价格降低一半后的每斤利润和每亩利润
    df["Profit_per_Jin_Half"] = (df["AvgPrice"] / 2) - (
        df["Cost_per_Mu"] / df["Yield_per_Mu"]
    )
    df["Profit_per_Mu_Half"] = df["Profit_per_Jin_Half"] * df["Yield_per_Mu"]
    df = df[
        [
            "Index",
            "CropID",
            "CropName",
            "FieldType",
            "PlantingSeason",
            "Yield_per_Mu",
            "Cost_per_Mu",
            "MinPrice",
            "MaxPrice",
            "AvgPrice",
            "Profit_per_Mu",
            "Profit_per_Jin",
            "Profit_per_Mu_Half",
            "Profit_per_Jin_Half",
        ]
    ]
    df["Profit_per_Mu_Half"] = df["Profit_per_Mu_Half"].round(4)
    df["Profit_per_Jin_Half"] = df["Profit_per_Jin_Half"].round(4)
    # 保存结果到原CSV文件
    df.to_csv("data/降价利润.csv", index=False)


if __name__ == "__main__":
    pass
    # 处理利润()
    # 补全种植地缺
    # 添加销量列()
    处理总产量1()
    # 计算降价后利润()
